package DBLayer;

import ModelLayer.SalesLineItem;
import ModelLayer.SalesOrder;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Gruppe 2
 */
public class DBSalesLineItem extends DBExtentions implements IFDBSalesLineItem {

    private Connection con;
    private IFDBProduct dbPro;
    private ArrayList<String> queryList = new ArrayList<String>();

    public DBSalesLineItem()
    {
        con = DbConnection.getInstance().getDBcon();
        dbPro = new DBProduct();
    }

    public ArrayList<SalesLineItem> getAllSalesLineItems()
    {
        return miscWhere("");
    }

    public ArrayList<SalesLineItem> searchSalesLine(int itemID)
    {
        String wClause = "saleLineID = " + itemID;
        System.out.println("SearchSaleLineItemID " + wClause);
        return miscWhere(wClause);
    }

    public ArrayList<SalesLineItem> searchSalesLineBySaleID(int salesID)
    {
        String wClause = "salesID = " + salesID;
        System.out.println("searchSalesLineBySaleID " + wClause);
        return miscWhere(wClause);
    }

    public int insertSalesLineItem(SalesOrder order, SalesLineItem item)
    {
        queryList.clear();
        int nextSalesLineID = GetMax.getMaxId("Select max(salesLineID) from SalesLineItem");
        nextSalesLineID = nextSalesLineID + 1;
        System.out.println("next sale = " + nextSalesLineID);

        int rc = -1;
        //Transaktion 1 Begins
        String query = "INSERT INTO SalesLineItem(saleLineID, quantity, salesID, productID, lineTotal)  VALUES('"
                + nextSalesLineID + "','"
                + item.getSalesLineID() + "','"
                + item.getQuantity() + "','"
                + order.getSalesOrderID() + "', "
                + item.getProduct().getProductID() + "',"
                + item.getLineTotal();

        System.out.println("insert : " + query);
        queryList.add(query);
        //Transaktion 1 Ends
        rc = rollBack(queryList);
        return rc;
    }

    public int updateSalesLineItem(SalesLineItem item)
    {
        queryList.clear();
        SalesLineItem itemObj = item;
        int rc = -1;

        String query = "UPDATE SalesLineItem SET "
                + "quantity ='" + itemObj.getQuantity() + "', "
                + "productID ='" + itemObj.getProduct().getProductID() + "', "
                + "lineTotal ='" + itemObj.getLineTotal() + "', "
                + "WHERE salesLineID = '" + itemObj.getSalesLineID() + "'";
        System.out.println("Update query:" + query);

        rc = rollBack(queryList);
        return rc;
    }

    public int deleteSalesLineItem(int saleLineItemID)
    {
        queryList.clear();
        int rc = -1;
        //Transaktion 1 Begin
        String query = "DELETE FROM SalesLineItem WHERE salesLineID = '"
                + saleLineItemID + "'";
        System.out.println("Delete Ref " + query);
        //Transaktion 1 Ends
        rc = rollBack(queryList);
        return rc;
    }

    private ArrayList<SalesLineItem> miscWhere(String wClause)
    {
        ResultSet results;
        ArrayList<SalesLineItem> list = new ArrayList<SalesLineItem>();
        String query = buildQuery(wClause);
        try
        { // read the employee from the database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);
            while (results.next())
            {
                SalesLineItem itemObj = new SalesLineItem();
                itemObj = buildSalesLine(results);
                list.add(itemObj);
            }//end while
            stmt.close();
        }//slut try
        catch (Exception e)
        {
            System.out.println("Query exception - select: " + e);
            e.printStackTrace();
        }
        return list;
    }

    //Singelwhere is used when we only select one SalesLineItem
    private SalesLineItem singleWhere(String wClause)
    {
        ResultSet results;
        SalesLineItem itemObj = new SalesLineItem();
        String query = buildQuery(wClause);
        System.out.println(query);
        try
        { // read the employee from the database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if (results.next())
            {
                itemObj = buildSalesLine(results);
            }
        }//end try
        catch (Exception e)
        {
            System.out.println("Query exception: " + e);
        }
        return itemObj;
    }

    //method to build the query
    private String buildQuery(String wClause)
    {
        String query = "SELECT salesLineID, quantity, pro.productID, lineTotal FROM SalesLineItem item, Product pro";

        if (wClause.length() > 0)
        {
            query = query + " WHERE item.productID = pro.productID "
                    + " " + wClause;
        }
        return query;
    }

    private SalesLineItem buildSalesLine(ResultSet results)
    {
        SalesLineItem item = new SalesLineItem();
        try
        {
            item.setSalesLineID(results.getInt("salesLineID"));
            item.setQuantity(results.getInt("quantity"));
            item.setProduct(dbPro.findProduct(results.getInt("productID"), false));
        }
        catch (Exception e)
        {
            System.out.println("error in building the SalesLineItem object");
        }
        return item;
    }
}
